package com.dreamtour.mapper;

import com.dreamtour.entity.Users;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author xls
 * @since 2024-12-24
 */
public interface UsersMapper extends BaseMapper<Users> {
    // 使用注解自定义 SQL 查询

    @Select("SELECT " +
            "DATE_FORMAT(regTime, '%Y-%m-%d') as reg_date, " +
            "COUNT(*) as count " +
            "FROM users " +
            "WHERE regTime >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH) " +
            "GROUP BY reg_date " +
            "ORDER BY reg_date ASC")
    List<Map<String, Object>> getDailyRegistrations();

    @Select("SELECT " +
            "CASE " +
            "    WHEN uAge < 20 THEN '20以下' " +
            "    WHEN uAge BETWEEN 20 AND 30 THEN '20-30' " +
            "    WHEN uAge BETWEEN 30 AND 40 THEN '30-40' " +
            "    WHEN uAge BETWEEN 40 AND 50 THEN '40-50' " +
            "    ELSE '50以上' " +
            "END AS age_group, " +
            "COUNT(*) AS user_count " +
            "FROM users " +
            "GROUP BY age_group " +
            "ORDER BY age_group")
    List<Map<String, Object>> getAgeDistribution();
}
